\documentclass{article}
\usepackage[utf8]{inputenc}
\usepackage{listings}
\usepackage[most]{tcolorbox}
\usepackage{geometry} % set margin
\geometry{left=3cm, right=2.5cm, top=2.5cm, bottom=2.5cm}

\newtcblisting{commandshell}{colback=white,colupper=black,colframe=black!75!black,
listing only,listing options={language=sh, breaklines=true,aboveskip=0pt, belowskip=0pt},
every listing line={\small\ttfamily\bfseries{[oracle@tp-shchai]\$} }}

\newtcblisting{sqlshell}{colback=white,colupper=black,colframe=black!75!black,
listing only,listing options={language=SQL, breaklines=true, aboveskip=0pt, belowskip=0pt},
every listing line={\small\ttfamily\bfseries{SQL> }}}

\newtcblisting{messageshell}{colback=white,colupper=black,colframe=black!75!black,
listing only,listing options={language={}, basicstyle=\small\ttfamily, breaklines=true,aboveskip=0pt, belowskip=0pt},
every listing line={}}



\title{Database Management Experiment Report \#2}
\author{Shitong CHAI}
\date{\vspace{-5ex}}
\begin{document}
\maketitle
\section{Control file}
\begin{enumerate}
\item Where is located the control file and what is its name ?
\begin{sqlshell}
show parameters control_files;
\end{sqlshell}
\begin{messageshell}
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_files                          string        /opt/oracle/oradata/ORCLCDB/control01.ctl, /opt/oracle/oradata/ORCLCDB/control02.ctl

\end{messageshell}
There are two control files: "/opt/oracle/oradata/ORCLCDB/control01.ctl" and "/opt/oracle/oradata/ORCLCDB/control02.ctl"

\item Try to start the database (startup) without the control file
\begin{commandshell}
cd /opt/oracle/oradata/ORCLCDB/
mv control01.ctl control01_backup.ctl
mv control02.ctl control02_backup.ctl
\end{commandshell}
\begin{sqlshell}
startup;
\end{sqlshell}
\begin{messageshell}
ORACLE instance started.

Total System Global Area  771747944 bytes
Fixed Size                  8900712 bytes
Variable Size                633339904 bytes
Database Buffers         121634816 bytes
Redo Buffers                  7872512 bytes
ORA-00205: error in identifying control file, check alert log for more info

\end{messageshell}
\item Multiplex the existing control file
\begin{commandshell}
mkdir DISK2
cp control01_backup.ctl DISK2/control01.ctl
cp control02_backup.ctl DISK2/control02.ctl
\end{commandshell}
\begin{sqlshell}
startup;
alter system set control_files= '/opt/oracle/oradata/ORCLCDB/DISK2/control01.ctl', '/opt/oracle/oradata/ORCLCDB/DISK2/control02.ctl' scope=spfile;
\end{sqlshell}
\begin{messageshell}

System altered.

\end{messageshell}
\begin{sqlshell}
show parameter control_files;
\end{sqlshell}
\begin{messageshell}

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_files                          string        /opt/oracle/oradata/ORCLCDB/DISK2/control01.ctl, /opt/oracle/oradata/ORCLCDB/DISK2/control
\end{messageshell}
\end{enumerate}
\section{Log file}
\begin{enumerate}
\item List the number and the name of existing log files. Display the number of log file group and members for each group.
\begin{itemize}
\item Number of existing log files:
\begin{sqlshell}
select count(*) from (select distinct group#, member from v$logfile);
\end{sqlshell}
\begin{messageshell}

  COUNT(*)
----------
        3

\end{messageshell}

\item Name of existing log files:
\begin{sqlshell}
select member from v$logfile;
\end{sqlshell}
\begin{messageshell}

MEMBER
--------------------------------------------------------------------------------
/opt/oracle/oradata/ORCLCDB/redo03.log
/opt/oracle/oradata/ORCLCDB/redo02.log
/opt/oracle/oradata/ORCLCDB/redo01.log

\end{messageshell}


\item Number of log file group:
\begin{sqlshell}
select count(distinct group#) from v$logfile;
\end{sqlshell}
\begin{messageshell}

COUNT(DISTINCTGROUP#)
---------------------
                  3

\end{messageshell}
\item Member from each group:
\begin{sqlshell}
select group#, member from v$logfile;
\end{sqlshell}
\begin{messageshell}
    GROUP#
----------
MEMBER
--------------------------------------------------------------------------------
        3
/opt/oracle/oradata/ORCLCDB/redo03.log

        2
/opt/oracle/oradata/ORCLCDB/redo02.log

        1
/opt/oracle/oradata/ORCLCDB/redo01.log

\end{messageshell}
\end{itemize}
\item ARCHIVELOG mode is enabled in your database ?
\begin{sqlshell}
select log_mode from v$database;
\end{sqlshell}
\begin{messageshell}

LOG_MODE
------------
NOARCHIVELOG

\end{messageshell}
So ARCHIVELOG mode is not enabled in my database.

\item Add a member for each group in your database 
\begin{sqlshell}
alter database add logfile member '/opt/oracle/oradata/ORCLCDB/redo01a.log' to group 1;
alter database add logfile member '/opt/oracle/oradata/ORCLCDB/redo02a.log' to group 2;
alter database add logfile member '/opt/oracle/oradata/ORCLCDB/redo03a.log' to group 3;
\end{sqlshell}
\begin{messageshell}

Database altered.


Database altered.


Database altered.

\end{messageshell}
\begin{sqlshell}
select group#, member from v$logfile;
\end{sqlshell}
\begin{messageshell}
    GROUP#
----------
MEMBER
--------------------------------------------------------------------------------
        3
/opt/oracle/oradata/ORCLCDB/redo03.log

        2
/opt/oracle/oradata/ORCLCDB/redo02.log

        1
/opt/oracle/oradata/ORCLCDB/redo01.log


    GROUP#
----------
MEMBER
--------------------------------------------------------------------------------
        1
/opt/oracle/oradata/ORCLCDB/redo01a.log

        2
/opt/oracle/oradata/ORCLCDB/redo02a.log

        3
/opt/oracle/oradata/ORCLCDB/redo03a.log


6 rows selected.

\end{messageshell}
\item Create a new log file group with two members in the directory DISK4 and verify its existence.
\begin{commandshell}
mkdir DISK4
\end{commandshell}
\begin{sqlshell}
alter database add logfile ('/opt/oracle/oradata/ORCLCDB/DISK4/redo04.log', '/opt/oracle/oradata/ORCLCDB/DISK4/redo04a.log') size 4m;
\end{sqlshell}
\begin{messageshell}

Database altered.

\end{messageshell}
\begin{commandshell}
ls DISK4
\end{commandshell}
\begin{messageshell}
redo04a.log  redo04.log
\end{messageshell}
\item Move the members “redo0la.log” and “redo02a.log” in the directory DISK4 .
\begin{sqlshell}
shutdown;
\end{sqlshell}
\begin{commandshell}
mv redo01a.log DISK4/
mv redo02a.log DISK4/
sqlplus / as sysdba
\end{commandshell}
\begin{sqlshell}
startup mount;
alter database rename file '/opt/oracle/oradata/ORCLCDB/redo01a.log', '/opt/oracle/oradata/ORCLCDB/redo02a.log' to '/opt/oracle/oradata/ORCLCDB/DISK4/redo01a.log', '/opt/oracle/oradata/ORCLCDB/DISK4/redo02a.log';
\end{sqlshell}
\begin{messageshell}
Database altered.

\end{messageshell}
\begin{sqlshell}
alter database open;
\end{sqlshell}
\begin{messageshell}
Database altered.

\end{messageshell}
\begin{sqlshell}
select group#, member from v$logfile where group#<=2;
\end{sqlshell}
\begin{messageshell}

    GROUP#
----------
MEMBER
--------------------------------------------------------------------------------
        2
/opt/oracle/oradata/ORCLCDB/redo02.log

        1
/opt/oracle/oradata/ORCLCDB/redo01.log

        1
/opt/oracle/oradata/ORCLCDB/DISK4/redo01a.log


    GROUP#
----------
MEMBER
--------------------------------------------------------------------------------
        2
/opt/oracle/oradata/ORCLCDB/DISK4/redo02a.log


\end{messageshell}
\item Remove the created log file group of the question 4.
\begin{sqlshell}
alter database drop logfile group 4;
\end{sqlshell}
\begin{messageshell}
Database altered.

\end{messageshell}
\begin{sqlshell}
select group#, member from v$logfile where group#=4;
\end{sqlshell}
\begin{messageshell}

no rows selected

\end{messageshell}
\end{enumerate}
\end{document}
